#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
投资决策分析工具
基于多维度数据提供投资分析支持

⚠️ 重要声明：
本工具仅提供数据分析功能，不构成投资建议。
投资有风险，决策需谨慎，请咨询专业投资顾问。
"""

import pandas as pd
import numpy as np
from datetime import datetime, date, timedelta
from sqlalchemy import create_engine, text
import warnings
warnings.filterwarnings('ignore')

from db_config import DBConfig

class InvestmentAnalyzer:
    """投资决策分析器"""

    def __init__(self):
        """初始化数据库连接"""
        self.db_config = DBConfig.config
        self.engine = create_engine(
            f"mysql+pymysql://{self.db_config['user']}:{self.db_config['password']}@{self.db_config['host']}:{self.db_config['port']}/{self.db_config['database']}?charset={self.db_config['charset']}"
        )

    def get_stock_basic_info(self, stock_code):
        """获取股票基本信息"""
        try:
            query = """
            SELECT * FROM stock_basic
            WHERE stock_code = %s
            """
            df = pd.read_sql(query, self.engine, params=[stock_code])
            return df
        except Exception as e:
            print(f"获取股票基本信息失败: {e}")
            return pd.DataFrame()

    def get_stock_price_data(self, stock_code, days=30):
        """获取股票价格数据"""
        try:
            end_date = date.today()
            start_date = end_date - timedelta(days=days)

            query = """
            SELECT * FROM stock_day
            WHERE stock_code = %s
            AND trade_date BETWEEN %s AND %s
            ORDER BY trade_date
            """
            df = pd.read_sql(query, self.engine, params=[stock_code, start_date, end_date])
            return df
        except Exception as e:
            print(f"获取股票价格数据失败: {e}")
            return pd.DataFrame()

    def get_financial_data(self, stock_code):
        """获取财务数据"""
        try:
            query = """
            SELECT * FROM stock_finance
            WHERE secucode = %s
            ORDER BY report_date DESC
            LIMIT 5
            """
            df = pd.read_sql(query, self.engine, params=[stock_code])
            return df
        except Exception as e:
            print(f"获取财务数据失败: {e}")
            return pd.DataFrame()

    def get_margin_data(self, stock_code, days=30):
        """获取融资融券数据"""
        try:
            end_date = date.today()
            start_date = end_date - timedelta(days=days)

            query = """
            SELECT * FROM stock_margin_detail
            WHERE stock_code = %s
            AND trade_date BETWEEN %s AND %s
            ORDER BY trade_date
            """
            df = pd.read_sql(query, self.engine, params=[stock_code, start_date, end_date])
            return df
        except Exception as e:
            print(f"获取融资融券数据失败: {e}")
            return pd.DataFrame()

    def get_dividend_data(self, stock_code):
        """获取分红数据"""
        try:
            query = """
            SELECT * FROM finance_dividend
            WHERE stock_code = %s
            ORDER BY ex_dividend_date DESC
            LIMIT 10
            """
            df = pd.read_sql(query, self.engine, params=[stock_code])
            return df
        except Exception as e:
            print(f"获取分红数据失败: {e}")
            return pd.DataFrame()

    def calculate_technical_indicators(self, price_df):
        """计算技术指标"""
        if price_df.empty:
            return {}

        df = price_df.copy()
        indicators = {}

        try:
            # 简单移动平均线
            df['MA5'] = df['close_price'].rolling(window=5).mean()
            df['MA10'] = df['close_price'].rolling(window=10).mean()
            df['MA20'] = df['close_price'].rolling(window=20).mean()

            # 相对强弱指标(RSI)
            delta = df['close_price'].diff()
            gain = (delta.where(delta > 0, 0)).rolling(window=14).mean()
            loss = (-delta.where(delta < 0, 0)).rolling(window=14).mean()
            rs = gain / loss
            df['RSI'] = 100 - (100 / (1 + rs))

            # MACD指标
            exp1 = df['close_price'].ewm(span=12, adjust=False).mean()
            exp2 = df['close_price'].ewm(span=26, adjust=False).mean()
            df['MACD'] = exp1 - exp2
            df['Signal'] = df['MACD'].ewm(span=9, adjust=False).mean()
            df['Histogram'] = df['MACD'] - df['Signal']

            # 布林带
            df['BB_Middle'] = df['close_price'].rolling(window=20).mean()
            df['BB_Std'] = df['close_price'].rolling(window=20).std()
            df['BB_Upper'] = df['BB_Middle'] + (df['BB_Std'] * 2)
            df['BB_Lower'] = df['BB_Middle'] - (df['BB_Std'] * 2)

            # 最新指标值
            latest = df.iloc[-1]

            indicators.update({
                'MA5': round(latest['MA5'], 2) if not pd.isna(latest['MA5']) else None,
                'MA10': round(latest['MA10'], 2) if not pd.isna(latest['MA10']) else None,
                'MA20': round(latest['MA20'], 2) if not pd.isna(latest['MA20']) else None,
                'RSI': round(latest['RSI'], 2) if not pd.isna(latest['RSI']) else None,
                'MACD': round(latest['MACD'], 4) if not pd.isna(latest['MACD']) else None,
                'MACD_Signal': round(latest['Signal'], 4) if not pd.isna(latest['Signal']) else None,
                'BB_Upper': round(latest['BB_Upper'], 2) if not pd.isna(latest['BB_Upper']) else None,
                'BB_Lower': round(latest['BB_Lower'], 2) if not pd.isna(latest['BB_Lower']) else None,
            })

        except Exception as e:
            print(f"计算技术指标失败: {e}")

        return indicators

    def analyze_margin_trading(self, margin_df):
        """分析融资融券数据"""
        if margin_df.empty:
            return {}

        analysis = {}

        try:
            latest = margin_df.iloc[-1]

            # 融资融券余额比例
            total_balance = latest.get('total_margin_balance', 0)
            financing_balance = latest.get('financing_balance', 0)
            lending_balance = latest.get('securities_lending_balance', 0)

            if total_balance > 0:
                analysis['financing_ratio'] = round(financing_balance / total_balance * 100, 2)
                analysis['lending_ratio'] = round(lending_balance / total_balance * 100, 2)

            # 融资融券余额变化趋势
            if len(margin_df) >= 5:
                recent_avg = margin_df['total_margin_balance'].tail(5).mean()
                prev_avg = margin_df['total_margin_balance'].head(len(margin_df) - 5).tail(5).mean() if len(margin_df) > 5 else recent_avg

                if prev_avg > 0:
                    change_pct = (recent_avg - prev_avg) / prev_avg * 100
                    analysis['margin_balance_trend'] = round(change_pct, 2)

            # 融资买入/融券卖出比例
            financing_buy = latest.get('financing_buy_amount', 0)
            lending_sell = latest.get('securities_lending_amount', 0)

            if financing_buy > 0:
                analysis['buy_sell_ratio'] = round(lending_sell / financing_buy, 2)

        except Exception as e:
            print(f"分析融资融券数据失败: {e}")

        return analysis

    def analyze_dividend_policy(self, dividend_df):
        """分析分红政策"""
        if dividend_df.empty:
            return {}

        policy = {}

        try:
            # 近3年分红统计
            recent_years = 3
            current_year = date.today().year
            recent_dividends = dividend_df[
                dividend_df['ex_dividend_date'].notna() &
                (pd.to_datetime(dividend_df['ex_dividend_date']).dt.year >= current_year - recent_years)
            ]

            if not recent_dividends.empty:
                # 分红频率
                policy['dividend_frequency'] = len(recent_dividends)

                # 平均股息率
                avg_yield = recent_dividends['dividend_yield'].mean()
                policy['avg_dividend_yield'] = round(avg_yield, 2) if not pd.isna(avg_yield) else None

                # 现金分红比例
                cash_dividends = recent_dividends[recent_dividends['cash_dividend_ratio'] > 0]
                policy['cash_dividend_ratio'] = round(len(cash_dividends) / len(recent_dividends) * 100, 1)

                # 平均现金分红比例
                avg_cash_ratio = recent_dividends['cash_dividend_ratio'].mean()
                policy['avg_cash_dividend_ratio'] = round(avg_cash_ratio, 2) if not pd.isna(avg_cash_ratio) else None

                # 送转统计
                bonus_shares = recent_dividends[recent_dividends['bonus_share_ratio'] > 0]
                policy['bonus_share_frequency'] = len(bonus_shares)

                # 分红稳定性评分
                years_with_dividend = recent_dividends['ex_dividend_date'].dt.year.nunique()
                policy['dividend_stability_score'] = round(years_with_dividend / recent_years * 100, 1)

        except Exception as e:
            print(f"分析分红政策失败: {e}")

        return policy

    def analyze_financial_health(self, financial_df):
        """分析财务健康状况"""
        if financial_df.empty:
            return {}

        health = {}

        try:
            latest = financial_df.iloc[0]  # 最新的财务数据

            # 盈利能力指标
            health['net_margin'] = latest.get('xsjll')  # 销售净利率
            health['roe'] = latest.get('roejq')  # ROE
            health['roa'] = latest.get('zzcjll')  # 总资产净利率

            # 偿债能力指标
            health['current_ratio'] = latest.get('ld')  # 流动比率
            health['quick_ratio'] = latest.get('sd')  # 速动比率
            health['debt_ratio'] = latest.get('zcfzl')  # 资产负债率

            # 成长能力指标
            health['revenue_growth'] = latest.get('yyzsrgdhbzc')  # 营收增长率
            health['profit_growth'] = latest.get('netprofitrphbzc')  # 净利润增长率

        except Exception as e:
            print(f"分析财务健康状况失败: {e}")

        return health

    def generate_investment_report(self, stock_code):
        """生成综合投资分析报告"""
        print(f"\n{'='*60}")
        print(f"📊 股票 {stock_code} 投资分析报告")
        print(f"{'='*60}")
        print(f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
        print(f"⚠️  本报告仅供参考，不构成投资建议")
        print(f"{'='*60}")

        # 获取各项数据
        basic_info = self.get_stock_basic_info(stock_code)
        price_data = self.get_stock_price_data(stock_code, 60)
        financial_data = self.get_financial_data(stock_code)
        margin_data = self.get_margin_data(stock_code, 30)
        dividend_data = self.get_dividend_data(stock_code)

        # 1. 基本信息
        if not basic_info.empty:
            stock_info = basic_info.iloc[0]
            print("\n📋 基本信息:")
            print(f"   股票名称: {stock_info.get('stock_name', 'N/A')}")
            print(f"   行业分类: {stock_info.get('industry', 'N/A')}")

        # 2. 技术分析
        if not price_data.empty:
            tech_indicators = self.calculate_technical_indicators(price_data)
            print("\n📈 技术指标:")
            print(f"   最新收盘价: {price_data.iloc[-1]['close_price']:.2f}")
            print(f"   MA5: {tech_indicators.get('MA5', 'N/A')}")
            print(f"   MA10: {tech_indicators.get('MA10', 'N/A')}")
            print(f"   MA20: {tech_indicators.get('MA20', 'N/A')}")
            print(f"   RSI: {tech_indicators.get('RSI', 'N/A')}")

            # RSI分析
            rsi = tech_indicators.get('RSI')
            if rsi:
                if rsi > 70:
                    print("   RSI提示: 超买区间，注意风险")
                elif rsi < 30:
                    print("   RSI提示: 超卖区间，可能存在机会")
                else:
                    print("   RSI提示: 中性区间")

        # 3. 融资融券分析
        if not margin_data.empty:
            margin_analysis = self.analyze_margin_trading(margin_data)
            print("\n💰 融资融券分析:")
            print(f"   融资比例: {margin_analysis.get('financing_ratio', 'N/A')}%")
            print(f"   融券比例: {margin_analysis.get('lending_ratio', 'N/A')}%")
            print(f"   买卖比例: {margin_analysis.get('buy_sell_ratio', 'N/A')}")

            trend = margin_analysis.get('margin_balance_trend')
            if trend:
                if trend > 10:
                    print(f"   融资融券趋势: 余额增加 {trend:.1f}%，市场情绪偏乐观")
                elif trend < -10:
                    print(f"   融资融券趋势: 余额减少 {abs(trend):.1f}%，市场情绪偏谨慎")
                else:
                    print("   融资融券趋势: 变化不大")

        # 4. 财务分析
        if not financial_data.empty:
            financial_health = self.analyze_financial_health(financial_data)
            print("\n💼 财务健康状况:")
            print(f"   销售净利率: {financial_health.get('net_margin', 'N/A')}%")
            print(f"   ROE: {financial_health.get('roe', 'N/A')}%")
            print(f"   流动比率: {financial_health.get('current_ratio', 'N/A')}")
            print(f"   资产负债率: {financial_health.get('debt_ratio', 'N/A')}%")

            # 财务健康评分
            score = 0
            if financial_health.get('roe', 0) and financial_health['roe'] > 10:
                score += 1
            if financial_health.get('current_ratio', 0) and financial_health['current_ratio'] > 1.5:
                score += 1
            if financial_health.get('debt_ratio', 100) and financial_health['debt_ratio'] < 60:
                score += 1

            if score >= 2:
                print("   ✅ 财务健康评分: 良好")
            elif score == 1:
                print("   ⚠️ 财务健康评分: 一般")
            else:
                print("   ❌ 财务健康评分: 需关注")

        # 5. 分红分析
        if not dividend_data.empty:
            dividend_policy = self.analyze_dividend_policy(dividend_data)
            print("\n💰 分红政策分析:")
            print(f"   近3年分红次数: {dividend_policy.get('dividend_frequency', 'N/A')}")
            print(f"   平均股息率: {dividend_policy.get('avg_dividend_yield', 'N/A')}%")
            print(f"   现金分红比例: {dividend_policy.get('cash_dividend_ratio', 'N/A')}%")
            print(f"   送转频率: {dividend_policy.get('bonus_share_frequency', 'N/A')} 次")
            print(f"   分红稳定性: {dividend_policy.get('dividend_stability_score', 'N/A')}%")

            # 分红政策评分
            stability = dividend_policy.get('dividend_stability_score', 0)
            frequency = dividend_policy.get('dividend_frequency', 0)
            yield_rate = dividend_policy.get('avg_dividend_yield', 0)

            dividend_score = 0
            if stability >= 80:
                dividend_score += 1
            if frequency >= 2:
                dividend_score += 1
            if yield_rate and yield_rate >= 2:
                dividend_score += 1

            if dividend_score >= 2:
                print("   ✅ 分红政策评分: 优秀")
            elif dividend_score == 1:
                print("   ⚠️ 分红政策评分: 一般")
            else:
                print("   ❌ 分红政策评分: 需关注")

        print(f"\n{'='*60}")
        print("🎯 投资决策建议:")
        print("   本分析基于历史数据和量化指标")
        print("   请结合市场环境、个人风险偏好等因素综合考虑")
        print("   建议咨询专业投资顾问获取个性化建议")
        print(f"{'='*60}")

    def compare_stocks(self, stock_codes):
        """比较多只股票"""
        print(f"\n📊 股票对比分析 ({', '.join(stock_codes)})")
        print("=" * 80)

        comparison_data = []

        for stock_code in stock_codes:
            # 获取基本数据
            price_data = self.get_stock_price_data(stock_code, 30)
            margin_data = self.get_margin_data(stock_code, 30)
            financial_data = self.get_financial_data(stock_code)

            stock_info = {
                '股票代码': stock_code,
                '最新价格': price_data.iloc[-1]['close_price'] if not price_data.empty else None,
                '30日均价': price_data['close_price'].tail(30).mean() if not price_data.empty else None,
            }

            # 技术指标
            if not price_data.empty:
                tech = self.calculate_technical_indicators(price_data)
                stock_info['RSI'] = tech.get('RSI')

            # 融资融券
            if not margin_data.empty:
                margin_analysis = self.analyze_margin_trading(margin_data)
                stock_info['融资比例'] = margin_analysis.get('financing_ratio')

            # 财务数据
            if not financial_data.empty:
                financial = self.analyze_financial_health(financial_data)
                stock_info['ROE'] = financial.get('roe')
                stock_info['净利率'] = financial.get('net_margin')

            comparison_data.append(stock_info)

        # 转换为DataFrame并显示
        df = pd.DataFrame(comparison_data)
        print(df.to_string(index=False, float_format='%.2f'))

def main():
    """主函数"""
    analyzer = InvestmentAnalyzer()

    # 示例：分析单只股票
    stock_code = "000001"  # 平安银行
    analyzer.generate_investment_report(stock_code)

    # 示例：比较多只股票
    # stocks_to_compare = ["000001", "600000", "000002"]
    # analyzer.compare_stocks(stocks_to_compare)

if __name__ == "__main__":
    main()